﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 药品信息管理
{
    public partial class YP_CKPM : Form
    {
        public YP_CKPM()
        {
            InitializeComponent();
        }

        private void YP_CKPM_Load(object sender, EventArgs e)
        {
            db db1 = new db();
            int y = 5;
            int x = 6;
            DataTable dt = db1.sqldt("select  mc from yk_crlx");
            foreach (DataRow dr in dt.Rows)
            {
                CheckBox cb = new CheckBox();
                cb.Location = new Point(x, y);
                y = y + 23;
                if (y > 60)
                {
                    x = x + 90;
                    y = 5;
                }
                cb.Text = dr["mc"].ToString();
                cb.Checked = true;
                cb.Size = new Size(60, 16);
                panelEx1.Controls.Add(cb);
            }

            int h = 5;
            int j = 6;
            //db db1 = new db();
            //DataTable dt = db1.sqldt("select  mc from ZD_YPJB");
            //DataTable dt1 = db1.sqldt("select  mc from ZD_YPJB");
            DataTable dt1 = db1.sqldt("select  mc from ZD_YPJX");
            foreach (DataRow dr1 in dt1.Rows)
            {
                CheckBox cb1 = new CheckBox();
                cb1.Location = new Point(j, h);
                h = h + 23;
                if (h > 60)
                {
                    j = j + 90;
                    h = 5;
                }
                cb1.Text = dr1["mc"].ToString();
                cb1.Checked = true;
                cb1.Size = new Size(60, 16);
                panelEx2.Controls.Add(cb1);
            }



        }

        private void buttonItem1_Click(object sender, EventArgs e)
        {
            db db2 = new db();

            string time1 = dateTimePicker1.Value.ToString("yyyy.MM.dd"), time2 = dateTimePicker2.Value.ToString("yyyy.MM.dd");
            string string_cklx = " and CR.MC in ('') ", string_ypjx = " NR.JX in ('') ";

            foreach (Control aa in panelEx1.Controls)
            {
                CheckBox cb = (CheckBox)aa;
                if (cb.Checked == true)
                {
                    string_cklx = string_cklx.Replace("''", "'','" + cb.Text + "'");
                }

            }


            foreach (Control aa in groupBox1.Controls)
            {
                CheckBox cb = (CheckBox)aa;
                if (cb.Checked == true)
                {
                    string_ypjx = string_ypjx.Replace("''", "'','" + cb.Text + "'");
                }

            }
            string str_refsh = @"SELECT NR.BM 编码, NR.MC 名称, NR.GG 规格, NR.BZDW 单位, SUM(D1.SL) 数量, SUM(D1.JE) 金额
                                 FROM YK_CKD0 D0, YK_CKD1 D1, ZD_YP NR 
                                 WHERE D0.CKDH=D1.CKDH AND  NR.BM=D1.YPBM 
                                 AND D0.RQ BETWEEN 41061 AND 41090 
                                 AND D0.CKLX IN (16,17,18,19) 
                                 AND NR.JX IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33)

  
                                 GROUP BY NR.BM, NR.MC, NR.GG, NR.BZDW";
            dataGridView1.DataSource= db2.sqldt(str_refsh);
        }
    }
}